<?php

namespace App\Services\Stats\Game;

use App\Core\Traits\Numeral;
use App\Models\Game\Settlement\Info;
use App\Services\Service;
use App\Services\Traits\DatabaseMysql;
use App\Models\Admin\Stats\Settlement\Game as StatsSettlementGame;

class GameService extends Service
{
    use DatabaseMysql, Numeral;

	/**
	 * 获取数据和总条数
	 *
	 * @param \Illuminate\Database\Eloquent\Builder $query
	 * @return array
	 */
	private function getData($query)
	{
		$columns[] = 'currency';
		$columns[] = 'game_id';
		$columns[] = 'COUNT(*) as doc_count';
		$columns[] = 'COUNT(DISTINCT uid) as player_count';
		$columns[] = 'SUM(bet_num) as bet_num';
		$columns[] = 'IFNULL(SUM(gain_player),0) as gain_player';
		$columns[] = 'IFNULL(SUM(bet_base),0) as bet_base';
		$columns[] = 'IFNULL(SUM(bet_num),0) as bet_num';
		$columns[] = "CAST(SUM(usd->'$.amount') AS DECIMAL({$this->decimals()},{$this->precision()})) AS usd_amount";
		$columns[] = "CAST(SUM(usd->'$.gain_player') AS DECIMAL({$this->decimals()},{$this->precision()})) AS usd_gain_player";
		$columns[] = "CAST(SUM(usd->'$.bet_base') AS DECIMAL({$this->decimals()},{$this->precision()})) AS usd_bet_base";
		$columns[] = "CAST(SUM(usd->'$.bet_num') AS DECIMAL({$this->decimals()},{$this->precision()})) AS usd_bet_num";

		$query->groupBy('game_id', 'currency');

		return Info::dbConnection()->transaction(function () use ($query, $columns) {
			// 查询数据
			$collection = $query->getQuery()
				->selectRaw('SQL_CALC_FOUND_ROWS ' . implode(',', $columns))
				->orderBy('currency')
				->get();
			// 数据条数
			$total = (int)Info::dbConnection()->select("SELECT FOUND_ROWS() as `row_count`")[0]['row_count'];

			return [$collection, $total];
		});
	}

	/**
	 * 获取游戏统计列表
	 *
	 * @param \Illuminate\Http\Request $request
	 * @param bool $export
	 * @return \Illuminate\Database\Eloquent\Collection[]|int[]|array[]
	 */
	public function getList($request, $export = false)
	{
		$query = Info::query();

		$query->where('status', Info::STATUS_NORMAL)->where('player_type', Info::PLAYER_TYPE);

		// 筛选【时间】
		$this->makeWhereParamDatetimeTimeZone($request, $query, 'end_time');
		// 筛选【平台】
		$this->makeWhereParamOrg($request, $query);
		// 筛选【平台】【排除】
		$this->makeWhereParamNot($request, 'plat_code_exclude', $query, 'plat_code');
		// 筛选【游戏】
		$this->makeWhereParam($request, 'game_id', $query);
		// 筛选【游戏】【排除】
		$this->makeWhereParamNot($request, 'game_id_exclude', $query, 'game_id');

		// 筛选【币种】
		$this->makeWhereParam($request, 'currency', $query);
		// 筛选【币种】【排除】
		$this->makeWhereParamNot($request, 'currency_exclude', $query, 'currency');
		// 筛选【游戏】
		$this->makeWhereParam($request, 'game_id', $query);
		// 筛选【游戏】【排除】
		$this->makeWhereParamNot($request, 'game_id_exclude', $query, 'game_id');

		// 统计数据
		$stats = $this->getStats(clone $query);

		// 排序分页
		$this->makeOrderParam($request, $query);

		$this->makePaginationParam($request, $query);

		// 获取数据
		list($collection, $total) = $this->getData($query);

		return [
			$collection,
			$total,
			$stats
		];
	}

	/**
	 * 获取统计信息
	 *
	 * @param \Illuminate\Database\Eloquent\Builder $query
	 * @return array
	 */
	private function getStats($query)
	{
		// USD
		$usd = (clone $query)->getQuery()->selectRaw(implode(',', [
			'COUNT(*) as doc_count',
			'COUNT(DISTINCT uid) as player_count',
			"-CAST(SUM(usd->'$.gain_player') AS DECIMAL({$this->decimals()},{$this->precision()})) AS amount",
			"CAST(SUM(usd->'$.gain_player') AS DECIMAL({$this->decimals()},{$this->precision()})) AS gain_player",
			"CAST(SUM(usd->'$.bet_base') AS DECIMAL({$this->decimals()},{$this->precision()})) AS bet_base",
			"CAST(SUM(usd->'$.bet_num') AS DECIMAL({$this->decimals()},{$this->precision()})) AS bet_num",
		]))->first();
		// USD【RTP】
		$usd['rtp'] = $this->makeRtp($usd['bet_num'], $usd['gain_player']);

		// 分币种
		$sts = (clone $query)->getQuery()->selectRaw(implode(',', [
			'COUNT(DISTINCT uid) as player_count',
			'COUNT(*) as doc_count',
			'-IFNULL(SUM(gain_player),0) as amount',
			'IFNULL(SUM(gain_player),0) as gain_player',
			'IFNULL(SUM(bet_base),0) as bet_base',
			'IFNULL(SUM(bet_num),0) as bet_num',
			'currency',
		]))
			->groupBy('currency')
			->get();
		// 分币种【RTP】
		$sts->transform(function ($item) {
			return $item + ['rtp' => $this->makeRtp($item['bet_num'], $item['gain_player'])];
		});

		return [
			'usd' => $usd,
			'sts' => $sts,
		];
	}

	/**
	 * 获取游戏统计USD
	 *
	 * @param \Illuminate\Http\Request $request
	 * @param bool $export
	 * @return \Illuminate\Database\Eloquent\Collection[]|array[]
	 */
	public function getUsd($request, $export = false)
	{
		$query = Info::query();

		$query->where('status', Info::STATUS_NORMAL)->where('player_type', Info::PLAYER_TYPE);
		// 筛选【时间】
		$this->makeWhereParamDatetimeTimeZone($request, $query, 'end_time');
		// 筛选【平台】
		$this->makeWhereParamOrg($request, $query);
		// 筛选【平台】【排除】
		$this->makeWhereParamNot($request, 'plat_code_exclude', $query, 'plat_code');
		// 筛选【游戏】
		$this->makeWhereParam($request, 'game_id', $query);
		// 筛选【游戏】【排除】
		$this->makeWhereParamNot($request, 'game_id_exclude', $query, 'game_id');


		$columns[] = 'game_id';
		$columns[] = 'COUNT(*) as doc_count';
		$columns[] = 'COUNT(DISTINCT uid) as player_count';
		$columns[] = "CAST(SUM(usd->'$.bet_base') AS DECIMAL({$this->decimals()},{$this->precision()})) AS bet_base";
		$columns[] = "CAST(SUM(usd->'$.bet_num') AS DECIMAL({$this->decimals()},{$this->precision()})) AS bet_num";
		$columns[] = "CAST(SUM(usd->'$.gain_player') AS DECIMAL({$this->decimals()},{$this->precision()})) AS gain_player";
		$columns[] = "-CAST(SUM(usd->'$.gain_player') AS DECIMAL({$this->decimals()},{$this->precision()})) AS amount";

		// 查询数据
		$collection = (clone $query)->getQuery()->selectRaw(implode(',', $columns))
			->groupBy('game_id')
			->orderBy(
				$request->input('order_column', 'doc_count'),
				$request->input('order', 'desc')
			)
			->get();

		// 合计USD
		$stats = $query->getQuery()->selectRaw(implode(',', [
			'COUNT(*) as doc_count',
			'COUNT(DISTINCT uid) as player_count',
			"-CAST(SUM(usd->'$.gain_player') AS DECIMAL({$this->decimals()},{$this->precision()})) AS amount",
			"CAST(SUM(usd->'$.gain_player') AS DECIMAL({$this->decimals()},{$this->precision()})) AS gain_player",
			"CAST(SUM(usd->'$.bet_base') AS DECIMAL({$this->decimals()},{$this->precision()})) AS bet_base",
			"CAST(SUM(usd->'$.bet_num') AS DECIMAL({$this->decimals()},{$this->precision()})) AS bet_num",
		]))->first();
		// USD【RTP】
		$stats['rtp'] = $this->makeRtp($stats['bet_num'], $stats['gain_player']);

		return [
			$collection,
			$stats
		];
	}
}
